﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Collections;
using System.Windows.Forms;

namespace Delonix_Regia_Hotel_Management_System.Class
{
    class DBManager
    {
        public static bool Login(string staffId, string staffPassword)
        {
            bool successful = false;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaConnectionString"].ConnectionString; 
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT *FROM Staff WHERE staffId=@staffId and staffPassword=@staffPassword";
                comm.Parameters.AddWithValue("@staffId", staffId);
                comm.Parameters.AddWithValue("@staffPassword", staffPassword);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read()) //dr.Read() will return trueif there is at least one row
                {
                    successful = true;
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return successful;
        }

        public static Staff GetStaffDetailsByID(string staffId)
        {
            Staff u = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Staff WHERE staffId=@staffId";
                comm.Parameters.AddWithValue("@staffId", staffId);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    u = new Staff();
                    u.position = (string)dr["position"];
                }

                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return u;
        }

      
        public static int CreateRoomBooking(Guest g, RoomBooking r)
        {
            int rowinserted = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT into Guest (nric, lastName, firstName, homeMailingAddress, email, phoneNum) values (@nric, @lastName, @firstName, @homeMailingAddress, @email, @phoneNum)";
                comm.CommandText += "INSERT into RoomBooking (bookingId, nric, numOfAdult, numOfChildren, roomType, checkInDate, checkoutDate, additionalRemarks, paymentMethod) values (@bookingId,@nric,@numOfAdult, @numOfChildren, @roomType, @checkInDate, @checkoutDate, @additionalRemarks, @paymentMethod)";
                comm.Parameters.AddWithValue("@nric", g.nric);
                comm.Parameters.AddWithValue("@lastName", g.lastName);
                comm.Parameters.AddWithValue("@firstName", g.firstName);
                comm.Parameters.AddWithValue("@homeMailingAddress", g.address);
                comm.Parameters.AddWithValue("@email", g.email);
                comm.Parameters.AddWithValue("@phoneNum", g.phoneNum);
                comm.Parameters.AddWithValue("@bookingId", r.bookingId);
                comm.Parameters.AddWithValue("@numOfAdult", r.numOfAdults);
                comm.Parameters.AddWithValue("@numOfChildren", r.numOfChildren);
                comm.Parameters.AddWithValue("@roomType", r.roomType);
                comm.Parameters.AddWithValue("@checkInDate", r.checkInDate);
                comm.Parameters.AddWithValue("@checkOutDate", r.checkOutDate);
                comm.Parameters.AddWithValue("@additionalRemarks", r.additionalRemarks);
                comm.Parameters.AddWithValue("@paymentMethod", r.paymentMethod);

                rowinserted = comm.ExecuteNonQuery();


                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowinserted;
        }

        public static List<Staff> getAllStaff()
        {
            SqlConnection conn = new SqlConnection();
            SqlCommand comm = new SqlCommand();
            List<Staff> staffList = new List<Staff>();
            try
            {
                conn.ConnectionString = "data source=localhost; initial catalog=DelonixRegia; user id=user; password=Password_2013";
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Staff";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Staff prod = new Staff(dr["staffId"].ToString(), dr["staffName"].ToString(), (DateTime)dr["dob"], dr["bankAccount"].ToString(), dr["homeAddress"].ToString(), (int)dr["phoneNum"], dr["position"].ToString(), dr["staffPassword"].ToString());
                    staffList.Add(prod);
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return staffList;
        }

        public static List<HouseKeeping> getAllHouseKeeping()
        {
            SqlConnection conn = new SqlConnection();
            SqlCommand comm = new SqlCommand();
            List<HouseKeeping> houseKeepingList = new List<HouseKeeping>();
            try
            {
                conn.ConnectionString = "data source=localhost; initial catalog=DelonixRegia; user id=user; password=Password_2013";
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM HouseKeeping";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    HouseKeeping prod = new HouseKeeping(dr["dutyType"].ToString(), (DateTime)dr["scheduleDate"], dr["staffId"].ToString());
                    houseKeepingList.Add(prod);
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return houseKeepingList;
        }

        public static int CreateStaff(Staff s)
        {
            int rowinserted = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT into Staff (staffId, staffName, dob, bankAccount, homeAddress, phoneNum, position,staffPassword) values (@staffId, @staffName, @dob, @bankAccount, @homeAddress, @phoneNum, @position,@staffPassword)";
                comm.Parameters.AddWithValue("@staffId", s.staffId);
                comm.Parameters.AddWithValue("@staffName", s.staffName);
                comm.Parameters.AddWithValue("@dob", s.dob);
                comm.Parameters.AddWithValue("@bankAccount", s.bankAccount);
                comm.Parameters.AddWithValue("@homeAddress", s.homeAddress);
                comm.Parameters.AddWithValue("@phoneNum", s.phoneNum);
                comm.Parameters.AddWithValue("@position", s.position);
                comm.Parameters.AddWithValue("@staffPassword", s.staffPassword);
                rowinserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowinserted;
        }

        public static Room CheckRoomAvailability(string enqStartDate, string enqEndDate, string enqRoomType)
        {
            Room r = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT roomNum, roomType FROM Room WHERE roomNum NOT IN (Select roomAllocated FROM RoomBooking WHERE (@enqStartDate = checkInDate OR @enqStartDate < checkInDate AND @enqEndDate > checkInDate OR @enqStartDate > checkInDate AND @enqStartDate < checkOutDate)) AND roomType = @enqRoomType;";
                comm.Parameters.AddWithValue("@enqStartDate", enqStartDate);
                comm.Parameters.AddWithValue("@enqEndDate", enqEndDate);
                comm.Parameters.AddWithValue("@enqRoomType", enqRoomType);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    r = new Room();
                    r.roomNum = (int)dr["roomNum"];
                    r.roomType = (string)dr["roomType"];
                }

                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return r;
        }

        public static List<Room> getAllRoom()
        {
            SqlConnection conn = new SqlConnection();
            SqlCommand comm = new SqlCommand();
            List<Room> RoomList = new List<Room>();
            try
            {
                conn.ConnectionString = "data source=localhost; initial catalog=DelonixRegia; user id=user; password=Password_2013";
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Room";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Room prod = new Room((int)dr["roomNum"], dr["roomStatus"].ToString(), (int)dr["adultInRoom"], (int)dr["childrenInRoom"],  dr["roomStartingDate"].ToString(), dr["roomEndingDate"].ToString(), dr["roomType"].ToString());
                    RoomList.Add(prod);
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return RoomList;
        }

        public static Room GetRoomDetailsByRoomNum(int roomNum)
        {
            Room u = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Room WHERE roomNum=@roomNum";
                comm.Parameters.AddWithValue("@roomNum", roomNum);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    u = new Room();
                    u.roomNum = (int)dr["roomNum"];
                    u.roomStatus = (string)dr["roomStatus"];
                    u.noOfAdult = (int)dr["adultInRoom"];
                    u.noofChild = (int)dr["childrenInRoom"];
                    u.roomStartingDate = (string)dr["roomStartingDate"];
                    u.roomEndingDate = (string)dr["roomEndingDate"];
                    u.roomType = (string)dr["roomType"];
                }

                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return u;
        }

        public static List<Room> getRoomByDate(string searchDate)
        {
            SqlConnection conn = new SqlConnection();
            SqlCommand comm = new SqlCommand();
            List<Room> RoomList = new List<Room>();
            try
            {
                conn.ConnectionString = "data source=localhost; initial catalog=DelonixRegia; user id=user; password=Password_2013";
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM [DelonixRegia].[dbo].[Room] WHERE @searchDate BETWEEN roomStartingDate AND roomEndingDate";
                comm.Parameters.AddWithValue("@searchDate", searchDate);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Room prod = new Room((int)dr["roomNum"], dr["roomStatus"].ToString(), (int)dr["adultInRoom"], (int)dr["childrenInRoom"], dr["roomStartingDate"].ToString(), dr["roomEndingDate"].ToString(), dr["roomType"].ToString());
                    RoomList.Add(prod);
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return RoomList;
        }

        public static Staff GetStaffID(string staffId)
        {
            Staff u = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Staff WHERE staffId=@staffId";
                comm.Parameters.AddWithValue("@staffId", staffId);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    u = new Staff();
                    u.staffId = (string)dr["staffId"];
                    u.staffName = (string)dr["staffName"];
                    u.dob = (DateTime)dr["dob"];
                    u.bankAccount = (string)dr["bankAccount"];
                    u.homeAddress = (string)dr["homeAddress"];
                    u.phoneNum = (int)dr["phoneNum"];
                    u.position = (string)dr["position"];


                }

                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return u;
        }

        public static bool checkStaffId(String staffID)
        {
            bool result = false;
            SqlConnection conn = new SqlConnection();
            SqlCommand comm = new SqlCommand();
            try
            {
                conn.ConnectionString = "data source=localhost; initial catalog=DelonixRegia; user id=user; password=Password_2013";
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Staff WHERE staffID = @staffID";
                comm.Parameters.AddWithValue("@staffID", staffID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    result = true;
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public static bool checkRoomNum(int roomNum)
        {
            bool result = false;
            SqlConnection conn = new SqlConnection();
            SqlCommand comm = new SqlCommand();
            try
            {
                conn.ConnectionString = "data source=localhost; initial catalog=DelonixRegia; user id=user; password=Password_2013";
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Room WHERE roomNum = @roomNum";
                comm.Parameters.AddWithValue("@roomNum", roomNum);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    result = true;
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public static List<RoomBooking> getAllRoomBooking()
        {
            SqlConnection conn = new SqlConnection();
            SqlCommand comm = new SqlCommand();
            List<RoomBooking> RoomList = new List<RoomBooking>();
            try
            {
                conn.ConnectionString = "data source=localhost; initial catalog=DelonixRegia; user id=user; password=Password_2013";
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM RoomBooking";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    RoomBooking rb = new RoomBooking(dr["bookingId"].ToString(), dr["nric"].ToString(), (int)dr["numOfAdult"], (int)dr["numOfChildren"], dr["roomType"].ToString(), dr["checkInDate"].ToString(), dr["checkOutDate"].ToString(), dr["additionalRemarks"].ToString(), dr["paymentMethod"].ToString(), (int)dr["roomAllocated"]);
                    RoomList.Add(rb);
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return RoomList;
        }

        public static List<RoomBooking> getAllRoomBookingByBookingId(string bookingId)
        {
            SqlConnection conn = new SqlConnection();
            SqlCommand comm = new SqlCommand();
            List<RoomBooking> RoomList = new List<RoomBooking>();
            try
            {
                conn.ConnectionString = "data source=localhost; initial catalog=DelonixRegia; user id=user; password=Password_2013";
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM RoomBooking WHERE bookingId=@bookingId";
                comm.Parameters.AddWithValue("@bookingId", bookingId);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    RoomBooking rb = new RoomBooking(dr["bookingId"].ToString(), dr["nric"].ToString(), (int)dr["numOfAdult"], (int)dr["numOfChildren"], dr["roomType"].ToString(), dr["checkInDate"].ToString(), dr["checkOutDate"].ToString(), dr["additionalRemarks"].ToString(), dr["paymentMethod"].ToString(), (int)dr["roomAllocated"]);
                    RoomList.Add(rb);
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return RoomList;
        }

        public static List<Guest> getAllGuestbyNric(string nric)
        {
            SqlConnection conn = new SqlConnection();
            SqlCommand comm = new SqlCommand();
            List<Guest> GuestList = new List<Guest>();
            try
            {
                conn.ConnectionString = "data source=localhost; initial catalog=DelonixRegia; user id=user; password=Password_2013";
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Guest WHERE nric = @nric";
                comm.Parameters.AddWithValue("@nric", nric);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Guest g = new Guest(dr["nric"].ToString(), dr["lastName"].ToString(), dr["firstName"].ToString(), (int)dr["phoneNum"], dr["homeMailingAddress"].ToString(), dr["email"].ToString());
                    GuestList.Add(g);
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return GuestList;
        }

        public static List<Guest> getAllGuest()
        {
            SqlConnection conn = new SqlConnection();
            SqlCommand comm = new SqlCommand();
            List<Guest> guest = new List<Guest>();
            try
            {
                conn.ConnectionString = "data source=localhost; initial catalog=DelonixRegia; user id=user; password=Password_2013";
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Guest";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Guest g = new Guest(dr["nric"].ToString(), dr["lastName"].ToString(), dr["firstName"].ToString(), (int)dr["phoneNum"], dr["homeMailingAddress"].ToString(), dr["email"].ToString());
                    guest.Add(g);
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return guest;
        }

        public static int CreateHousekeeping(HouseKeeping HK)
        {
            int rowinserted = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT into HouseKeeping (dutyType, scheduleDate, staffId) values (@dutyType, @scheduleDate, @staffId)";
                comm.Parameters.AddWithValue("@dutyType", HK.dutyType);
                comm.Parameters.AddWithValue("@scheduleDate", HK.scheduleDate);
                comm.Parameters.AddWithValue("@staffId", HK.staffId);
                rowinserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowinserted;

        }
    }
}
